﻿Public Class frm_list_pendaftar

    ' The column currently used for sorting.
    Private m_SortingColumn As ColumnHeader

    Sub init_column()

        With ListBox1

            'inti column
            .Columns.Clear()

            .Columns.Add("No").Width = 65
            .Columns.Add("Tanggal").Width = 90
            .Columns.Add("No Pendaftar").Width = 100
            .Columns.Add("No UN").Width = 100
            .Columns.Add("Asal Pendaftar").Width = 130
            .Columns.Add("Nama Peserta").Width = 150
            .Columns.Add("Jenis Kelamin").Width = 75
            .Columns.Add("Tempat Lahir").Width = 100
            .Columns.Add("Tanggal Lahir").Width = 100
            .Columns.Add("Asal Sekolah").Width = 100

            If APP.Info.Jenjang = "SMP" Then
                .Columns.Add("Nilai B. Indonesia").Width = 100
                .Columns.Add("Nilai Matematika").Width = 100
                .Columns.Add("Nilai I P A").Width = 100

            ElseIf APP.Info.Jenjang = "SMA" Then
                .Columns.Add("Nilai B. Indonesia").Width = 100
                .Columns.Add("Nilai Matematika").Width = 100
                .Columns.Add("Nilai I P A").Width = 100
                .Columns.Add("Nilai B. Inggris").Width = 100

            Else
                .Columns.Add("Nilai B. Indonesia").Width = 100
                .Columns.Add("Nilai Matematika").Width = 100
                .Columns.Add("Nilai I P A").Width = 100
                .Columns.Add("Nilai B. Inggris").Width = 100

                .Columns.Add("Nilai Agama").Width = 100
                .Columns.Add("Nilai IPS").Width = 100
                .Columns.Add("Nilai Pkn").Width = 100

            End If

            .Columns.Add("Pilihan 1").Width = 200
            .Columns.Add("Pilihan 2").Width = 200


        End With

    End Sub

    Sub init_data()

        ' init column
        Call init_column()
        ListBox1.Items.Clear()

        Dim i As Integer = 1

        DB.FlushCache()
        DB.Selects("*")
        DB.From("tbl_pendaftaran")

        Dim rcd As Odbc.OdbcDataReader

        rcd = Connection.ExecuteToDataReader(DB.GetQueryString)

        If rcd.HasRows Then
            With rcd
                While rcd.Read
                    ListBox1.Items.Add(i)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatDateTime(.Item("tanggal_pendaftaran").ToString, DateFormat.ShortDate))
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("no_pendaftaran").ToString)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("no_uan").ToString)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(IIf(.Item("asal_pendaftar").ToString = 0, "Kota Bandung", "Luar Kota Bandung"))
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("nama_pendaftar").ToString)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("jenis_kelamin").ToString)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("tempat_lahir").ToString)
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatDateTime(.Item("tanggal_lahir").ToString, DateFormat.ShortDate))
                    ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(.Item("asal_sekolah").ToString)

                    If APP.Info.Jenjang = "SMP" Then
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_indonesia").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_matematika").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_ipa").ToString, 2))

                    ElseIf APP.Info.Jenjang = "SMA" Then
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_indonesia").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_matematika").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_ipa").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_inggris").ToString, 2))

                    Else
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_indonesia").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_matematika").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_ipa").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_inggris").ToString, 2))

                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_agama").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_ips").ToString, 2))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(FormatNumber(.Item("n_pkn").ToString, 2))

                    End If

                    If APP.Info.Jenjang = "SMK" Then
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(ListSekolah.getJurusan(CInt(.Item("pilihan_1").ToString)))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(ListSekolah.getJurusan(CInt(.Item("pilihan_2").ToString)))

                    Else
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(ListSekolah.getNama(CInt(.Item("pilihan_1").ToString)))
                        ListBox1.Items(ListBox1.Items.Count - 1).SubItems.Add(ListSekolah.getNama(CInt(.Item("pilihan_2").ToString)))

                    End If
                    
                    i += 1
                End While
            End With
        End If

    End Sub

    Private Sub frm_list_pendaftar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call init_data()
    End Sub

    Private Sub ListBox1_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListBox1.ColumnClick
        ' Get the new sorting column.
        Dim new_sorting_column As ColumnHeader = _
            ListBox1.Columns(e.Column)

        ' Figure out the new sorting order.
        Dim sort_order As System.Windows.Forms.SortOrder
        If m_SortingColumn Is Nothing Then
            ' New column. Sort ascending.
            sort_order = SortOrder.Ascending
        Else
            ' See if this is the same column.
            If new_sorting_column.Equals(m_SortingColumn) Then
                ' Same column. Switch the sort order.
                If m_SortingColumn.Text.StartsWith("> ") Then
                    sort_order = SortOrder.Descending
                Else
                    sort_order = SortOrder.Ascending
                End If
            Else
                ' New column. Sort ascending.
                sort_order = SortOrder.Ascending
            End If

            ' Remove the old sort indicator.
            m_SortingColumn.Text = m_SortingColumn.Text.Substring(2)
        End If

        ' Display the new sort order.
        m_SortingColumn = new_sorting_column
        If sort_order = SortOrder.Ascending Then
            m_SortingColumn.Text = "> " & m_SortingColumn.Text
        Else
            m_SortingColumn.Text = "< " & m_SortingColumn.Text
        End If

        ' Create a comparer.
        ListBox1.ListViewItemSorter = New ListViewComparer(e.Column, sort_order)

        ' Sort.
        ListBox1.Sort()
    End Sub

    Private Sub ListBox1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.DoubleClick
        Try
            Dim no_pendaftar As String
            no_pendaftar = ListBox1.SelectedItems(0).SubItems(2).Text

            If APP.Info.Jenjang = "SMP" Then
                frm_smp_edit.Dispose()
                frm_smp_edit.InitData(no_pendaftar)
                frm_smp_edit.ShowDialog(frm_main)
                frm_smp_edit.Focus()

            ElseIf APP.Info.Jenjang = "SMA" Then
                frm_sma_edit.Dispose()
                frm_sma_edit.InitData(no_pendaftar)
                frm_sma_edit.ShowDialog(frm_main)
                frm_sma_edit.Focus()

            Else
                frm_smk_edit.Dispose()
                frm_smk_edit.InitData(no_pendaftar)
                frm_smk_edit.ShowDialog(frm_main)
                frm_smk_edit.Focus()

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Call init_data()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        
        Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

        Try
            With SaveFileDialog1

                Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
                Dim wBook As Microsoft.Office.Interop.Excel.Workbook
                Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
                Dim col As Integer = 0
                Dim row As Integer = 0

                .Title = "Export To Excel"
                .FileName = ""
                .Filter = "File Excel (*.xlsx)|*.xlsx"
                If .ShowDialog = Windows.Forms.DialogResult.OK Then

                    wBook = excel.Workbooks.Add()
                    wSheet = wBook.ActiveSheet()

                    'Init Column in excel
                    For col = 0 To ListBox1.Columns.Count - 1
                        excel.Cells(1, col + 1) = "'" & CStr(ListBox1.Columns.Item(col).Text)
                    Next

                    'Init data
                    For row = 0 To ListBox1.Items.Count - 1
                        For col = 0 To ListBox1.Columns.Count - 1
                            excel.Cells((row + 2), (col + 1)) = "'" & ListBox1.Items(row).SubItems(col).Text
                        Next
                    Next

                    wSheet.Columns("A:Z").AutoFit()
                    wBook.SaveAs(.FileName)
                    wBook.Close()
                    excel.Quit()
                    'excel.Workbooks.Open(strFileName)
                    'excel.Visible = True
                End If
            End With

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

        System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
    End Sub
End Class